package dao.impl;

import bean.Athlete;
import bean.Competition;
import bean.EntryForm;
import dao.AthleteDao;
import dao.CompetitionDao;
import dao.EntryFromDao;
import util.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;

/**
 * @Auther leiHua
 * @Date 2022/5/13 20:59
 */
public class EntryFormDaoImpl implements EntryFromDao {


    /**
     * 查询三个表，运动员表、比赛项目表、报名id表（join on 查询）
     * 将查询到运动员信息类和比赛信息类 放到一条报名信息中
     *
     * @return 返回所有的报名信息条  以arraylist数组形式
     */
    @Override
    public ArrayList<EntryForm> selectAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<EntryForm> entryList = new ArrayList<>();
        CompetitionDao competitionDao = new CompetitionDaoImpl();
        AthleteDao athleteDao = new AthleteDaoImpl();
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("select e.e_id,e.c_id,e.a_id,c.`name`as competitionName,place,number,time,a.`name`as athleteName,sex,age,department,e.status FROM entry_form e JOIN competition c ON c.c_id=e.c_id join athlete a on e.a_id=a.a_id ");
            rs = ps.executeQuery();
            while (rs.next()) {
                Competition compe = new Competition();
                Athlete ath = new Athlete();

                String c_id = rs.getString("c_id");

                //比赛项目信息
                String competitionName = rs.getString("competitionName");
                String place = rs.getString("place");
                int number = rs.getInt("number");
                String time = rs.getString("time");
                compe.setC_id(c_id);
                compe.setPalce(place);
                compe.setNumber(number);
                compe.setTime(time);
                compe.setCompetitionName(competitionName);

                //运动员信息类
                String a_id = rs.getString("a_id");
                String athleteName = rs.getString("athleteName");
                String sex = rs.getString("sex");
                String department = rs.getString("department");
                int age = rs.getInt("age");
                String status = rs.getString("status");
                ath.setName(athleteName);
                ath.setDepartment(department);
                ath.setA_id(a_id);
                ath.setAge(age);
                ath.setSex(sex);
                ath.setC_id(c_id);

                //将运动员信息和比赛项目加入该条 表名信息中
                String e_id = rs.getString("e_id");
                EntryForm entry = new EntryForm(ath, compe, e_id,status);
                entryList.add(entry);
            }
            return entryList;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, ps, rs);
        }
        return null;
    }

    /**
     * 传入运动员ID 比赛项目ID 和该条信息ID 写入报名表中
     * @param e_id  表名ID
     * @param c_id  项目ID
     * @param a_id  运动员ID
     * @return   是否成功
     */
    public boolean add(String e_id,String c_id,String a_id,String status){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("insert into entry_form values (?,?,?,?)");
            ps.setString(1, e_id);
            ps.setString(2, c_id);
            ps.setString(3, a_id);
            ps.setString(4, status);
            int i= ps.executeUpdate();
            if (i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, ps, rs);
        }
        return false;
    }


    /**
     * 修改 该条报名的数据
     * @param entryForm
     * @return
     */
    public boolean update(EntryForm entryForm){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("update entry_form set c_id=?,a_id=?,status=? where e_id=?");
            ps.setString(1, entryForm.getCompetition().getC_id());
            ps.setString(2, entryForm.getAthlete().getA_id());
            ps.setString(3, entryForm.getStatus());
            ps.setString(4, entryForm.getId());
            int i= ps.executeUpdate();
            if (i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, ps, rs);
        }
        return false;

    }

    /**
     * 根据报名记录ID查询
     * @param e_id
     * @return
     */
    public EntryForm select(String e_id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("select c_id,a_id,status from entry_form  where e_id=?");
            ps.setString(1,e_id );
            rs= ps.executeQuery();
            EntryForm entryForm = new EntryForm();
            if (rs.next()){
                String c_id = rs.getString("c_id");
                String a_id = rs.getString("a_id");
                String status=rs.getString("status");
                AthleteDaoImpl athleteDao = new AthleteDaoImpl();
                Athlete athletet = athleteDao.select(a_id);
                CompetitionDaoImpl competitionDao = new CompetitionDaoImpl();
                Competition competition = competitionDao.select(c_id);
                entryForm.setId(e_id);
                entryForm.setAthlete(athletet);
                entryForm.setCompetition(competition);
                entryForm.setStatus(status);
                return entryForm;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, ps, rs);
        }
       return null;
    }

    /**
     * 根据运动员的ID 在报名查询 该运动员参加了的项目ID
     * @param a_id  传来的运动员ID
     * @return  返回比赛项目的ID 列表
     */
    public ArrayList<String> selectBaseAthlete(String a_id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList c_idList=null;
        try {
            c_idList=new ArrayList();
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("select c_id from entry_form where a_id=? ");
            ps.setString(1, a_id);
            rs = ps.executeQuery();
            while(rs.next()){
                c_idList.add(rs.getString("c_id"));
            }
            return c_idList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, ps, rs);
        }

        return null;
    }

    /**
     * 根据比赛项目的ID查询   报名记录信息
     * @param c_id  比赛ID
     * @return
     */
    public EntryForm selectBaseCompetition(String c_id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("select e_id,a_id,status from entry_form  where c_id=?");
            ps.setString(1,c_id );
            rs= ps.executeQuery();
            EntryForm entryForm = new EntryForm();
            if (rs.next()){
                String e_id = rs.getString("e_id");
                String a_id = rs.getString("a_id");
                String status=rs.getString("status");
                AthleteDaoImpl athleteDao = new AthleteDaoImpl();
                Athlete athletet = athleteDao.select(a_id);
                CompetitionDaoImpl competitionDao = new CompetitionDaoImpl();
                Competition competition = competitionDao.select(c_id);
                entryForm.setId(e_id);
                entryForm.setAthlete(athletet);
                entryForm.setCompetition(competition);
                entryForm.setStatus(status);
                return entryForm;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, ps, rs);
        }
        return null;
    }
    public static void main(String[] args) {
        //测试
        EntryFormDaoImpl entryFromDao = new EntryFormDaoImpl();
//        ArrayList<EntryForm> entryForms = entryFromDao.selectAll();
//        for (EntryForm entryForm : entryForms) {
//            System.out.println(entryForm.getAthlete().toString());
//            System.out.println(entryForm.getCompetition().toString());
//        }

        //add()
//        boolean add = entryFromDao.add("5", "5", "5");
//        System.out.println(add);
    }
}
